<?php
header("Content-Type: text/html; charset=iso-8859-1");
require_once('include/db_connection.php');
require_once('include/script_includes.php');

if(isset($_GET['request'])) {
	$request = $_GET['request'];
}

if ($request == 'getNameForID') {
	$playerID = $_GET['playerID'];
	$result = "unbekannter Spieler";
	
	$sql = "select firstName, lastName, contraction FROM players WHERE id=".$playerID." LIMIT 1";
	$res = $mysqli->query($sql);
	if ( ($row = $res->fetch_assoc()) != false ) {
		if($row['contraction'] != "") { 
			$result = $row['contraction']; 
		} else {
			$result = $row['firstName']." ".$row['lastName'];
		}
	}
	
	echo $result;
	exit;
}

if ($request == 'statisticHitsPlayer') {
	$playerID = $_GET['playerID'];
	$sql = "select game_score_v2.player_id,count(*) count,game_score_v2.score_multiplier,game_score_v2.score,concat(game_score_v2.score_multiplier,' x ',game_score_v2.score) scorex from game_score_v2, games_v2, games
			where games_v2.id = games.idNew
			and games_v2.id = game_score_v2.game_id
			and games_v2.gameGroup = 'X01'
			and game_score_v2.score >=0
			and player_id = ".$playerID."
			group by player_id,scorex
			order by player_id,count desc
			limit 7";
	$res = $mysqli->query($sql);
	$ohlc = array();
	$start = "";
	$score = array();
	$count = array();
	while (($row = $res->fetch_assoc()) != false) {
		$scorex = str_replace('1 x ','',$row['scorex']);
		$scorex = str_replace('0 x ','',$scorex);
		array_push($score,$scorex);
		array_push($count,$row['count']);
	}
	$ohlc = array($score,$count);
	echo json_encode( $ohlc );
	exit;
}
if ($request == 'statisticHitsPlayerPie') {
	$playerID = $_GET['playerID'];
	$sql = "select game_score_v2.player_id,count(*) count,game_score_v2.score_multiplier,game_score_v2.score,concat(game_score_v2.score_multiplier,' x ',game_score_v2.score) scorex from game_score_v2, games_v2, games
			where games_v2.id = games.idNew
			and games_v2.id = game_score_v2.game_id
			and games_v2.gameGroup = 'X01'
			and game_score_v2.score >=0
			and player_id = ".$playerID."
			group by player_id,scorex
			order by player_id,count desc
			limit 7";
	$res = $mysqli->query($sql);
	$ohlc = array();
	$start = "";
	$score = array();
	$count = array();
	while (($row = $res->fetch_assoc()) != false) {
		$score = str_replace('1 x ','',$row['scorex']);
		$score = str_replace('0 x ','',$score);
		array_push($ohlc,array($score,intval($row['count'])));
	}
	echo json_encode( $ohlc );
	exit;
}
if ($request == 'statisticWeeksPlayer') {
	$playerID = $_GET['playerID'];
	$sql = "select STR_TO_DATE(CONCAT(sub.year,sub.kw-1,'Monday'), '%X%V %W') date, sum(sub.score)/count(*) score_abs,max(sub.score) max,min(sub.score) min
			from (
			SELECT data.year,data.kw,data.day,data.score
			from (select year,kw,day(date) day,firstName,player_id,sum(score_abs)/count(*) score,date FROM `view_score_kw` WHERE player_id = ".$playerID." group by year,kw, day) data
			)sub
			group by sub.year,sub.kw
			order by sub.year desc,sub.kw desc
			limit 24";
	$res = $mysqli->query($sql);
	$ohlc = array();
	$start = "";
	while (($row = $res->fetch_assoc()) != false) {
		if($start=="") {
			$start=round($row['score_abs'],2);
		}
		array_push($ohlc,array($row['date'],round($row['score_abs'],2),round($row['max'],2),round($row['min'],2),$start));
		$start = round($row['score_abs'],2);
	}
	echo json_encode( $ohlc );
	exit;
}
if ($request == 'dartsPerWeek') {
	$sql = "select week(creation_date,1) kw,count(*) count from game_score_v2 group by kw";
	$res = $mysqli->query($sql);
	$line ="[[";
	while (($row = $res->fetch_assoc()) != false) {
		$line = $line.$row['count'].",";
	}
	$line = rtrim($line, ",");
	$line = $line."]]";
	echo $line;
	exit;
}
if ($request == 'statisticHitsAll') {
	$sql = "select game_score_v2.player_id,count(*) count,game_score_v2.score_multiplier,game_score_v2.score,concat(game_score_v2.score_multiplier,'x',game_score_v2.score) scorex from game_score_v2, games_v2, games
			where games_v2.id = games.idNew
			and games_v2.id = game_score_v2.game_id
			and games_v2.gameGroup = 'X01'
			and game_score_v2.score >=0
			group by scorex
			order by count desc
			limit 10";
	$res = $mysqli->query($sql);
	$ohlc = array();
	$start = "";
	$score = array();
	$count = array();
	while (($row = $res->fetch_assoc()) != false) {
		$scorex = str_replace('1x','',$row['scorex']);
		$scorex = str_replace('0x','',$scorex);
		array_push($score,$scorex);
		array_push($count,$row['count']);
	}
	$ohlc = array($score,$count);
	echo json_encode( $ohlc );
	exit;
}
if ($request == 'statisticWeeks') {
	?>
	<div id="chart1" style="margin:20px;height:400px; width:700px;"></div>
	<script type="text/javascript">
		<?php
		foreach ($_GET['playerid'] as $key => $value) {
			$plID = $value;
			$sql = "SELECT * 
					from (select firstName,player_id,score_abs,STR_TO_DATE(CONCAT(year,kw-1,'Monday'), '%X%V %W') date FROM `view_score_kw_winner`) data
					where data.score_abs != 'null'
					and player_id = ".$plID."
					order by date desc
					limit 24";
			$res = $mysqli->query($sql);
			$line ="var line".$plID."=[";
			while (($row = $res->fetch_assoc()) != false) {
				$line = $line."['".$row['date']."',".$row['score_abs']."],";
			}
			$line = rtrim($line, ",");
			$line = $line."];";
			echo $line;
		}
		$lines = "";
		$lables = "";
		foreach ($_GET['playerid'] as $key => $value) {
			$lines = $lines."line".$value.",";
			$sql = "SELECT * from players where id = ".$value;
			$res = $mysqli->query($sql);
			$row = $res->fetch_assoc();
			$lables = $lables."'".$row['firstName']."',";
		}
		$lines = rtrim($lines, ",");
		$lables = rtrim($lables, ",");
		?>

		var plot1 = $.jqplot ('chart1', [<?=$lines?>],{
			seriesDefaults:{
				yaxis:'y2axis'		
			},
			axes: {
				xaxis: {
					renderer: $.jqplot.DateAxisRenderer,
					tickOptions: { formatString: '%d.%m.%y' }
				},
				y2axis: {
					min:0,
					max:1,
					ticks:[0, 0.25, 0.5, 0.75, 1],
					tickOptions:{ prefix: '' }
				}
			},
			legend: {
				labels: [<?=$lables?>],
				renderer: $.jqplot.EnhancedLegendRenderer,
				location:'se',
				show: true
			}, 
			cursor:{ 
				show: true,
				zoom:true, 
				showTooltip:true
			} 
		});
	</script>
	<?php

	exit;
}

if ($request == 'statisticByPlayer') {
	$playerID = $_GET['playerID'];
	games_table($mysqli, null, $playerID, null,$_GET['manr']);
	mysqli_close($mysqli);
	exit;
}
if ($request == 'statisticByGame') {
	$gameID = $_GET['gameID'];
	games_table($mysqli, null, null, $gameID,$_GET['manr']);
	mysqli_close($mysqli);
	exit;
}
if ($request == 'statisticByGameAndPlayer') {
	$gameID = $_GET['gameID'];
	$playerID = $_GET['playerID'];
	games_table($mysqli, null, $playerID, $gameID,$_GET['manr']);
	mysqli_close($mysqli);
	exit;
}
if ($request == 'topGames') {
	$playerID = $_GET['playerID'];
	
	$res = $mysqli->query("select concat(firstName,' ',lastName) name from players where id =".$playerID);
	$row = $res->fetch_assoc();
	$playerName = $row['name'];
	
	echo $playerName."<br><br>";
	
	$sql = null;
	$minGamesCount = 5;
	if ($playerID != null) {
		$sqlTop10 = "select * from (select games.game_name Name, count(game_id) Anz, round(sum(score/vgm.max)/count(game_id),3) Abs from game_data, games, view_games_data_max_score vgm where game_data.id = vgm.id and game_data.game_id = games.id and player_id = ".$playerID." group by games.game_name order by Abs desc) gd where gd.Anz > ".$minGamesCount." limit 10";
		$sqlLess10 = "select * from (select games.game_name Name, count(game_id) Anz, round(sum(score/vgm.max)/count(game_id),3) Abs from game_data, games, view_games_data_max_score vgm where game_data.id = vgm.id and game_data.game_id = games.id and player_id = ".$playerID." group by games.game_name order by Abs asc) gd where gd.Anz > ".$minGamesCount." limit 10";
	} 
	echo "Top 10<br>";
	getResultTable($mysqli, $sqlTop10);
	echo "<br>Flop 10<br>";
	getResultTable($mysqli, $sqlLess10);
	mysqli_close($mysqli);
	exit;
}
if ($request == 'gamedetails') {
	$gameUUID = $_GET['gameUUID'];
	
	$sql = "select round Runde,firstName Name,dart+1 Dart,concat(score_multiplier,' x ',score) Punkte,score_multiplier*score Summe from game_score_v2, players where game_score_v2.player_id = players.id and game_uuid = ".$gameUUID." order by creation_date";
	getResultTable($mysqli, $sql);
	mysqli_close($mysqli);
	exit;
}

function getResultTable($mysqli, $sql) {
	print("<div id='sqlResultTable'>");
	print("<table class='gridtable-stistics'>");
	if ($result = $mysqli->query($sql)) {
		$finfo = $result->fetch_fields();

		foreach ($finfo as $val) {
			print("<th>".$val->name."</th>");
		}
		while (($rows = $result->fetch_assoc()) != false) {
			print("<tr>");
			foreach ($rows as $colName=>$data) {
				if($colName == 'keyname'){
					doStuff();
				}else{
					print("<td nowrap><font size=2>". $data . "</font></td>");
				}
			}
			print("</tr>");
        }

		$result->close();
	}	
	
	print("</table>");
	print("</div>");
}

?>